package com.wc.test.dao;

import java.util.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.wc.test.bean.User;
import com.wc.test.util.JDBCUtils;

/**
 * User数据处理层
 * 
 * @author ysh
 */
public class UserDaoImpl implements UserDao {

	/**
	 * 单例模式的饿汉式，空间换时间，无论你用不用都已经创建
	 */
	// 私有的构造函数，保证外类不能实例化本类
	private UserDaoImpl() {
	}

	// 自己创建一个类的实例化
	private static final UserDaoImpl userDaoImpl = new UserDaoImpl();

	// 创建一个get方法，返回一个实例
	public static UserDaoImpl getInstance() {
		return userDaoImpl;
	}

	public int findCount() throws Exception {
		// 总记录数
		int count = 0;
		PreparedStatement psd = null;
		ResultSet rst = null;
		String sql = "SELECT COUNT(1) FROM user";
		rst = JDBCUtils.execute(psd, rst, sql, null);
		System.out.println("SQL执行语句：" + sql);
		while (rst.next()) {
			count = rst.getInt(1);
		}
		JDBCUtils.close(null, psd, rst);
		// 返回总记录数
		return count;
	}

	public int saveUser(User user) throws Exception {
		int result = 0;
		PreparedStatement psd = null;
		Object[] params = {user.getUsername(), user.getPassword() };
		String sql = "INSERT INTO user(username,password) VALUES(?,?)";
		result = JDBCUtils.execute(psd, sql, params);
		System.out.println("SQL执行语句：" + sql + "\n?==>{"+user.getUsername() + ","+ user.getPassword() + "}");
		JDBCUtils.close(null, psd, null);
		return result;
	}

	public int removeUser(int id) throws Exception {
		int result = 0;
		PreparedStatement psd = null;
		Object[] params = { id };
		String sql = "DELETE FROM user WHERE id = ?";
		result = JDBCUtils.execute(psd, sql, params);
		System.out.println("SQL执行语句：" + sql + "\n?==>{" + id + "}");
		JDBCUtils.close(null, psd, null);
		return result;
	}

	public int updateUser(User user) throws Exception {
		int result = 0;
		PreparedStatement psd = null;
		Object[] params = { user.getUsername(), user.getPassword(), user.getId() };
		String sql = "UPDATE user SET username=?,password=? WHERE id=?";
		result = JDBCUtils.execute(psd, sql, params);
		System.out.println("SQL执行语句：" + sql + "\n?==>{" + user.getUsername() + "," + user.getPassword() + ","
				+ user.getId() + "}");
		JDBCUtils.close(null, psd, null);
		return result;
	}

	public User getUserByID(int id) throws Exception {
		PreparedStatement psd = null;
		ResultSet rst = null;
		User user = null;
		Object[] params = { id };
		String sql = "SELECT * FROM user WHERE id = ?";
		rst = JDBCUtils.execute(psd, rst, sql, params);
		System.out.println("SQL执行语句：" + sql + "\n?==>{" + id + "}");
		while (rst.next()) {
			user = new User();
			user.setId(rst.getInt("id"));
			user.setUsername(rst.getString("username"));
			user.setPassword(rst.getString("password"));
		}
		return user;
	}


	public List<User> getUserList(String userName, String isSort, int currentPageNo, int pageSize) throws Exception {
		PreparedStatement psd = null;
		ResultSet rst = null;
		//用于拼接sql
		StringBuffer sql = new StringBuffer();
		// 创建List进行存值并返回
		List<User> userList = new ArrayList<User>();
		//定义一个列表，用于存放参数
		List<Object> list = new ArrayList<Object>();
		sql.append("SELECT * FROM user WHERE 1=1");
		if (userName != null) {
			sql.append(" AND username LIKE ?");
			list.add("%" + userName + "%");
		}
		sql.append(" ORDER BY id " + isSort + " LIMIT ?,?");
		currentPageNo = (currentPageNo - 1) * pageSize;
		list.add(currentPageNo);
		list.add(pageSize);
		//把list转换成数组
		Object[] params = list.toArray();
		try {
			rst = JDBCUtils.execute(psd, rst, sql.toString(),params);
			System.out.println("SQL执行语句：" + sql.toString());
			while (rst.next()) {
				User user = new User();
				// 对user对象属性赋值
				user.setId(rst.getInt("id"));
				user.setUsername(rst.getString("username"));
				user.setPassword(rst.getString("password"));
				// 最后将对象数据存到list集合中
				userList.add(user);
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return userList;

	}

	public User getLoginUser(String username) throws Exception {
		PreparedStatement psd = null;
		ResultSet rst = null;
		User user = null;
		Object[] params = { username };
		String sql = "SELECT * FROM user WHERE username = ?";
		rst = JDBCUtils.execute(psd, rst, sql, params);
		System.out.println("SQL执行语句：" + sql + "\n?==>{" + username + "}");
		while (rst.next()) {
			user = new User();
			user.setId(rst.getInt("id"));
			user.setUsername(rst.getString("username"));
			user.setPassword(rst.getString("password"));
		}
		return user;
	}

	public boolean validateUserName(String username) throws Exception {
		//如果查询到返回true，反之返回false
		boolean flag = false;
		PreparedStatement psd = null;
		ResultSet rst = null;
		String sql = "SELECT * FROM user WHERE username = ?";
		Object[] params = {username};
		try {
			rst = JDBCUtils.execute(psd, rst, sql,params);
			System.out.println("SQL执行语句：" + sql);
			while (rst.next()) {
				flag = true;
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return flag;
	}
	
	public boolean validateUserId(int userId) throws Exception {
		//如果查询到返回true，反之返回false
		boolean flag = false;
		PreparedStatement psd = null;
		ResultSet rst = null;
		String sql = "SELECT * FROM user WHERE id = ?";
		Object[] params = {userId};
		try {
			rst = JDBCUtils.execute(psd, rst, sql,params);
			System.out.println("SQL执行语句：" + sql);
			while (rst.next()) {
				flag = true;
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return flag;
	}
	
	public boolean validateUserPwd(String password) throws Exception {
		//如果查询到返回true，反之返回false
		boolean flag = false;
		PreparedStatement psd = null;
		ResultSet rst = null;
		String sql = "SELECT * FROM user WHERE password = ?";
		Object[] params = {password};
		try {
			rst = JDBCUtils.execute(psd, rst, sql,params);
			System.out.println("SQL执行语句：" + sql);
			while (rst.next()) {
				flag = true;
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return flag;
	}
}
